﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Corsat.Library.Logic;

namespace Corsat.Library.DataManager
{
    public static partial class Main
    {
        #region Public Methods
        public static DataTable GetAllAccountsWithCorsatId(int Year)
        {
            try
            {
                command.CommandText = StoredProcedure.sp_GetAllAccountsWithCorsatId.ToString();
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();

                SqlParameter yearParam = new SqlParameter("Year", Year);

                command.Parameters.Add(yearParam);

                DataTable dtAccounts = new DataTable();

                adapter.Fill(dtAccounts);

                return dtAccounts;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        public static void SaveTechnologyResult(int CourseId, int AccountId, float Result, int GradeId, DateTime TestDate)
        {
            string updateTechResult = @"
                                        UPDATE AccountsDetails SET TechnologyCourseResult = " + Result.ToString()
                                                      + @", TechnologyCourseGradeId = "
                                                      + GradeId.ToString()
                                                      + @", TechnologyCourseId = "
                                                      + CourseId.ToString()
                                                      + @", TechnologyCourseTestDate = '" + TestDate.Date.ToString()
                                                      + @"' WHERE AccountId = " + AccountId.ToString() + @"AND [Year] = " + DateTime.Now.Year;

            command.CommandText = updateTechResult;
            command.CommandType = CommandType.Text;
            OpenConnection();
            command.ExecuteNonQuery();
            CloseConnection();
        }

        public static DataTable GetTechnologyResults(int CourseId, DateTime TestDate)
        {
            command.CommandText = @"
select AccountsDetails.CorsatId, AccountsMaster.ArabicName, AccountsDetails.TechnologyCourseResult, Grades.ItemName
from AccountsMaster inner join AccountsDetails 
on AccountsMaster.AccountId = AccountsDetails.AccountId
inner join Items Grades on AccountsDetails.TechnologyCourseGradeId = Grades.ItemId
Where AccountsDetails.TechnologyCourseId = " + CourseId + @" AND AccountsDetails.TechnologyCourseTestDate = '" + TestDate.Date.ToString() + @"' order by AccountsMaster.ArabicName asc";

            command.CommandType = CommandType.Text;

            DataTable dtResults = new DataTable();

            adapter.Fill(dtResults);

            return dtResults;
        }


        public static DataTable GetTechnologyResults(int courseId, int year, DateTime? examDate)
        {
            #region Select command Text

            command.CommandText = StoredProcedure.sp_GetTechnologyResults.ToString();
            command.CommandType = CommandType.StoredProcedure;

            #endregion

            #region Parameters

            command.Parameters.Clear();
            if (courseId > 0)
            {
                command.Parameters.Add("@CourseId", SqlDbType.Int).Value = courseId;
            }
            if (year > 0)
            {
                command.Parameters.Add("@Year", SqlDbType.Int).Value = year;
            }
            if (examDate != null)
            {
                command.Parameters.Add("@ExamDate", SqlDbType.DateTime).Value = examDate.Value.Date;
            };

            #endregion

            #region Get Data

            try
            {
                DataTable dtResults = new DataTable();
                adapter.SelectCommand = command;
                adapter.Fill(dtResults);

                if (dtResults != null && dtResults.Rows.Count > 0)
                    return dtResults;
                else
                    return null;
            }
            catch (Exception exc)
            {
                return null;
            }

            #endregion
        }

        public static Logic.AccountResults GetAccountResults(int CorsatId)
        {
            try
            {
                command.CommandText = StoredProcedure.sp_GetAccountResults.ToString();
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();

                command.Parameters.Add(new SqlParameter("CorsatId", CorsatId));
                command.Parameters.Add(new SqlParameter("Year", DateTime.Now.Year));

                DataSet dsResults = new DataSet();

                adapter.Fill(dsResults);

                Logic.AccountResults result = new Logic.AccountResults();

                if (dsResults.Tables.Count > 0)
                {

                    if (dsResults.Tables[0].Rows.Count > 0)
                        result.Name = dsResults.Tables[0].Rows[0]["ArabicName"].ToString();

                }

                if (dsResults.Tables.Count > 1 && dsResults.Tables[1].Rows.Count > 0)
                    result.TechResult = dsResults.Tables[1];

                if (dsResults.Tables.Count > 2 && dsResults.Tables[2].Rows.Count > 0)
                    result.CoursesResults = dsResults.Tables[2];
                return result;
            }
            catch (Exception exc)
            {
                return new Logic.AccountResults();
            }
        }

        public static DataTable GetAccountsForResults(int CourseId, int Year)
        {
            #region Select command Text

            command.CommandText = @"
select AccountsMaster.AccountId, AccountCourses.AccountSerialInCourse Serial, AccountsDetails.CorsatId, AccountsMaster.ArabicName, AccountCourses.Result, Items.ItemName Grade
from AccountsDetails inner join AccountCourses on AccountsDetails.AccountId = AccountCourses.AccountId
and AccountsDetails.[YEAR] = " + Year.ToString() + @"
inner join AccountsMaster on AccountsMaster.AccountId = AccountsDetails.AccountId
left outer join Items on AccountCourses.GradeId = Items.ItemId
where 
accountsdetails.TechnologycourseResult is not null and AccountCourses.[YEAR] = " + Year.ToString() + @" and
 (CAST(Att_1 as INT) 
+	CAST(Att_2 as INT) 
+	CAST(Att_3 as INT) 
+	CAST(Att_4 as INT) 
+	CAST(Att_5 as INT) 
+	CAST(Att_6 as INT) 
+	CAST(Att_7 as  INT)) > 2
and CourseId = " + CourseId.ToString() + @"
order by Serial
";
            command.CommandType = CommandType.Text;

            #endregion

            #region Parameters



            #endregion

            #region Get Data

            try
            {
                DataTable dtSerials = new DataTable();

                adapter.Fill(dtSerials);

                if (dtSerials != null && dtSerials.Rows.Count > 0)
                    return dtSerials;
                else
                    return null;
            }
            catch (Exception exc)
            {
                return null;
            }

            #endregion
        }

        public static void SaveResults(int CourseId, int Year, List<KeyValuePair<object, object>> Results)
        {
            command.CommandText = "";
            command.CommandType = CommandType.Text;
            command.Parameters.Clear();

            for (int i = 0; i < Results.Count; i++)
            {
                command.CommandText += @"

                UPDATE AccountCourses SET Result = ";

                if (Results[i].Value == null || Results[i].Value == DBNull.Value || Results[i].Value == "")
                    command.CommandText += " NULL ";
                else
                {
                    command.CommandText += ((float)Convert.ToDouble(Results[i].Value)).ToString();
                    command.CommandText += ", GradeId = ";

                    float Result = ((float)Convert.ToDouble(Results[i].Value));
                    int GradeId = 0;

                    if (Result >= 85)
                        GradeId = 315;
                    else if (Result >= 75)
                        GradeId = 314;
                    else if (Result >= 65)
                        GradeId = 313;
                    else if (Result >= 50)
                        GradeId = 312;
                    else
                        GradeId = 316;

                    if (GradeId > 0)
                        command.CommandText += GradeId.ToString();
                    else
                        command.CommandText += "NULL";
                }

                command.CommandText += " WHERE CourseId = " + CourseId.ToString() + " AND Year = " + Year.ToString() + " AND AccountId = " + Results[i].Key.ToString();
            }

            OpenConnection();

            command.ExecuteNonQuery();

            CloseConnection();
        }


        public static bool UpdateAccountCourseGrade(int accountId, int courseId, float result)
        {
            try
            {
                command.CommandText = StoredProcedure.sp_UpdateAccountCourseGrade.ToString();
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();

                int gradeId = 0;

                if (result >= 85)
                    gradeId = 315;
                else if (result >= 75)
                    gradeId = 314;
                else if (result >= 65)
                    gradeId = 313;
                else if (result >= 50)
                    gradeId = 312;
                else
                    gradeId = 316;

                if (accountId > 0)
                {
                    command.Parameters.Add(CreateParameter("@AccountId", accountId));
                }

                command.Parameters.Add(CreateParameter("@Year", DateTime.Now.Year));

                if (courseId > 0)
                {
                    command.Parameters.Add(CreateParameter("@CourseId", courseId));
                }
                //if (result > 0)
                //{
                    command.Parameters.Add(CreateParameter("@Result", result));
                //}
                if (gradeId > 0)
                {
                    command.Parameters.Add(CreateParameter("@GradeId", gradeId));
                }



                OpenConnection();
                object objAccountId = command.ExecuteScalar();
                CloseConnection();


                return true;
            }
            catch
            {
                return false;
            }
        }


        public static bool CanAttendCourseId(int accountId, int courseId)
        {
            try
            {
                command.CommandText = StoredProcedure.sp_CanAttendCourseId.ToString();
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();

                if (accountId > 0)
                {
                    command.Parameters.Add(CreateParameter("@AccountId", accountId));
                }

                command.Parameters.Add(CreateParameter("@Year", DateTime.Now.Year));

                if (courseId > 0)
                {
                    command.Parameters.Add(CreateParameter("@CourseId", courseId));
                }

                DataTable dtResults = new DataTable();

                adapter.Fill(dtResults);

                if (dtResults.Rows.Count > 0)
                    return true;

                return false;
            }
            catch (Exception exc)
            {
                return false;
            }
        }



        public static DataTable GetAccountResultsByAccountId(int accountId)
        {
            try
            {
                command.CommandText = StoredProcedure.sp_GetAccountResultsByAccountId.ToString();
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();

                command.Parameters.Add(new SqlParameter("AccountId", accountId));

                DataSet dsResults = new DataSet();

                adapter.Fill(dsResults);

                if (dsResults.Tables.Count > 0)
                    return dsResults.Tables[0];

                return null;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        #endregion

        public static DataTable GetCourseQuestions(int courseId)
        {
            #region Select command Text

            command.CommandText = StoredProcedure.sp_GetQuestionsByCourseId.ToString();
            command.CommandType = CommandType.StoredProcedure;

            #endregion

            #region Parameters

            command.Parameters.Clear();
            if (courseId > 0)
            {
                command.Parameters.Add("@CourseId", SqlDbType.Int).Value = courseId;
            }

            #endregion

            #region Get Data

            try
            {
                DataTable dtResults = new DataTable();
                adapter.SelectCommand = command;
                adapter.Fill(dtResults);

                if (dtResults != null && dtResults.Rows.Count > 0)
                    return dtResults;
                else
                    return null;
            }
            catch (Exception exc)
            {
                return null;
            }

            #endregion
        }

        public static Question GetCourseQuestionData(int questionId)
        {
            #region Select command Text

            command.CommandText = StoredProcedure.sp_GetQuestionDataByQuestionId.ToString();
            command.CommandType = CommandType.StoredProcedure;

            #endregion

            #region Parameters

            command.Parameters.Clear();
            if (questionId > 0)
            {
                command.Parameters.Add("@QuestionId", SqlDbType.Int).Value = questionId;
            }

            #endregion

            #region Get Data
            Question question = null;
            try
            {
                DataSet dsResults = new DataSet();
                adapter.SelectCommand = command;
                adapter.Fill(dsResults);


                DataRow drQuestion = dsResults.Tables[0].Rows[0];

                question = new Question();
                question.Text = drQuestion[0].ToString();
                question.CourseId = int.Parse(drQuestion[1].ToString());
                question.QuestionLevelId = int.Parse(drQuestion[2].ToString());


                DataTable drAnswersTable = dsResults.Tables[1];
                List<QuestionAnswer> questionAnswers = new List<QuestionAnswer>();

                for (int i = 0; i < drAnswersTable.Rows.Count; i++)
                {
                    DataRow drAnswer = drAnswersTable.Rows[i];
                    // Number, TeleTypeId, TeleTypeName
                    QuestionAnswer questionAnswer = new QuestionAnswer();
                    questionAnswer.AnswerText = drAnswer["AnswerText"] == DBNull.Value
                                                    ? ""
                                                    : drAnswer["AnswerText"].ToString();

                    bool isRight = false;
                    if (drAnswer["IsRightAnswer"] != DBNull.Value )
                    {
                        bool.TryParse(drAnswer["IsRightAnswer"].ToString(), out isRight);
                    }
                    questionAnswer.IsRightAnswer = isRight;
                    int answerId = 0;
                    if (drAnswer["AnswerId"] != DBNull.Value &&
                        int.TryParse(drAnswer["AnswerId"].ToString(), out answerId) &&
                        answerId > 0)
                    {
                        questionAnswer.AnswerId = answerId;
                    }
                    questionAnswers.Add(questionAnswer);
                }
                question.Answers = questionAnswers;
                return question;

            }
            catch (Exception exc)
            {
                return null;
            }

            #endregion
        }

        public static int SaveCourseQuestion(Question question)
        {
            try
            {
                command.CommandType = CommandType.StoredProcedure;

                if (question.QuestionId == 0)
                {
                    command.CommandText = StoredProcedure.sp_InsertCourseQuestion.ToString();
                }
                else
                    command.CommandText = StoredProcedure.sp_UpdateCourseQuestion.ToString();

                command.Parameters.Clear();

                if (question.QuestionId != 0) command.Parameters.Add(CreateParameter("@QuestionId", question.QuestionId));
                command.Parameters.Add(CreateParameter("@CourseId", question.CourseId));
                command.Parameters.Add(CreateParameter("@QuestionText", question.Text));
                command.Parameters.Add(CreateParameter("@QuestionLevelId", question.QuestionLevelId));

                DataTable dt = new DataTable();
                dt.Columns.Add("AnswerText");
                dt.Columns.Add("QuestionId");
                dt.Columns.Add("IsRightAnswer");
                foreach (QuestionAnswer answer in question.Answers)
                {
                    DataRow dr = dt.NewRow();
                    dr[0] = answer.AnswerText;
                    dr[1] = question.QuestionId;
                    dr[2] = answer.IsRightAnswer;
                    dt.Rows.Add(dr);
                }
                command.Parameters.Add(CreateParameter("@Answers", dt));

                OpenConnection();
                object objQuestionId = command.ExecuteScalar();

              /*  int AccountId = Convert.ToInt32(objAccountId);
                if (queryComplementary.Trim().Length != 0)
                {
                    command.Parameters.Clear();

                    command.CommandText = queryComplementary;
                    command.Parameters.Add(CreateParameter("SSN", Account.SSN));
                    command.CommandType = CommandType.Text;

                    command.ExecuteNonQuery();
                }*/

                CloseConnection();

                if (objQuestionId == DBNull.Value || Convert.ToInt32(objQuestionId) == 0)
                    return 0;
                else
                    return Convert.ToInt32(objQuestionId);
            }
            catch (Exception exc)
            {
                return 0;
            }
        }


        public static bool DeleteQuestion(int questionId)
        {
            #region Select command Text

            command.CommandText = StoredProcedure.sp_DeleteQuestion.ToString();
            command.CommandType = CommandType.StoredProcedure;

            #endregion

            #region Parameters

            command.Parameters.Clear();
            if (questionId > 0)
            {
                command.Parameters.Add("@QuestionId", SqlDbType.Int).Value = questionId;
            }

            #endregion

            #region Get Data

            try
            {
                OpenConnection();
                 command.ExecuteNonQuery();
                CloseConnection();
                return true;
            }
            catch (Exception exc)
            {
                return false;
            }

            #endregion
        }



        public static List<Question> GetCourseRandomExam(int courseId)
        {
            #region Select command Text

            command.CommandText = StoredProcedure.sp_GetRandomExamByCourseId.ToString();
            command.CommandType = CommandType.StoredProcedure;

            #endregion

            #region Parameters

            command.Parameters.Clear();
            if (courseId > 0)
            {
                command.Parameters.Add("@CourseId", SqlDbType.Int).Value = courseId;
            }

            #endregion

            #region Get Data
            try
            {
                DataSet dsResults = new DataSet();
                adapter.SelectCommand = command;
                adapter.Fill(dsResults);


                DataTable drAnswersTable = dsResults.Tables[1];
                List<QuestionAnswer> questionAnswers = new List<QuestionAnswer>();

                for (int i = 0; i < drAnswersTable.Rows.Count; i++)
                {
                    DataRow drAnswer = drAnswersTable.Rows[i];
                    // Number, TeleTypeId, TeleTypeName
                    QuestionAnswer questionAnswer = new QuestionAnswer();
                    questionAnswer.AnswerText = drAnswer["AnswerText"] == DBNull.Value
                                                    ? ""
                                                    : drAnswer["AnswerText"].ToString();

                    bool isRight = false;
                    if (drAnswer["IsRightAnswer"] != DBNull.Value)
                    {
                        bool.TryParse(drAnswer["IsRightAnswer"].ToString(), out isRight);
                    }
                    questionAnswer.IsRightAnswer = isRight;
                    int answerId = 0;
                    if (drAnswer["AnswerId"] != DBNull.Value &&
                        int.TryParse(drAnswer["AnswerId"].ToString(), out answerId) &&
                        answerId > 0)
                    {
                        questionAnswer.AnswerId = answerId;
                    }

                    int questionId = 0;
                    if (drAnswer["QuestionId"] != DBNull.Value &&
                        int.TryParse(drAnswer["QuestionId"].ToString(), out questionId) &&
                        questionId > 0)
                    {
                        questionAnswer.QuestionId = questionId;
                    }
                    questionAnswers.Add(questionAnswer);
                }

                List<Question> questions = new List<Question>();
                foreach (DataRow dataRow in dsResults.Tables[0].Rows)
                {
                    DataRow drQuestion = dataRow;
                    Question question = new Question
                                            {
                                                QuestionId = int.Parse(drQuestion["QuestionId"].ToString()),
                                                Text = drQuestion["QuestionText"].ToString(),
                                                CourseId = int.Parse(drQuestion["CourseId"].ToString()),
                                                QuestionLevelId = int.Parse(drQuestion["QuestionLevelId"].ToString())
                                            };
                    question.Answers = questionAnswers.FindAll(x => x.QuestionId == question.QuestionId);
                    questions.Add(question);
                }

                return questions;

            }
            catch (Exception exc)
            {
                return null;
            }

            #endregion
        }


        public static List<QuestionAnswer> GetQuestionRightAnswers(List<int> questionIds,out int attentedDays,int accountId , int courseId)
        {
            #region Select command Text

            command.CommandText = StoredProcedure.sp_GetQuestionsRightAnswers.ToString();
            command.CommandType = CommandType.StoredProcedure;

            attentedDays = 0;

            #endregion

            #region Parameters

            command.Parameters.Clear();

            DataTable dt = new DataTable();
            dt.Columns.Add("QuestionId");
            foreach (int id in questionIds)
            {
                DataRow dr = dt.NewRow();
                dr[0] = id;
                dt.Rows.Add(dr);
            }
            command.Parameters.Add(CreateParameter("@QuestionIds", dt));
            command.Parameters.Add(CreateParameter("@AccountId", accountId));
            command.Parameters.Add(CreateParameter("@CourseId", courseId));
            command.Parameters.Add(CreateParameter("@Year", DateTime.Now.Year));
            

            #endregion

            #region Get Data
            try
            {
                DataSet dsResults = new DataSet();
                adapter.SelectCommand = command;
                adapter.Fill(dsResults);

                DataTable drAnswersTable = dsResults.Tables[0];
                List<QuestionAnswer> questionAnswers = new List<QuestionAnswer>();

                for (int i = 0; i < drAnswersTable.Rows.Count; i++)
                {
                    DataRow drAnswer = drAnswersTable.Rows[i];
                    // Number, TeleTypeId, TeleTypeName
                    QuestionAnswer questionAnswer = new QuestionAnswer();
                    int answerId = 0;
                    if (drAnswer["AnswerId"] != DBNull.Value &&
                        int.TryParse(drAnswer["AnswerId"].ToString(), out answerId) &&
                        answerId > 0)
                    {
                        questionAnswer.AnswerId = answerId;
                    }
                    int questionId = 0;
                    if (drAnswer["QuestionId"] != DBNull.Value &&
                        int.TryParse(drAnswer["QuestionId"].ToString(), out questionId) &&
                        questionId > 0)
                    {
                        questionAnswer.QuestionId = questionId;
                    }
                    questionAnswers.Add(questionAnswer);
                }

                int attendanceCount = 0;
                DataTable attendaceTable = dsResults.Tables[1];
                if (attendaceTable != null && attendaceTable.Rows.Count>0)
                {
                        if (attendaceTable.Rows[0]["Att_SUM"] != DBNull.Value &&
                        int.TryParse(attendaceTable.Rows[0]["Att_SUM"].ToString(), out attendanceCount))
                    {
                        attentedDays = attendanceCount;
                    }
                }

                return questionAnswers;

            }
            catch (Exception exc)
            {
                return null;
            }

            #endregion
        }
    }
}
